package com.example.demo.mapper;

import com.example.demo.model.TestRecord;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface TestRecordMapper {
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "WHERE r.record_id = #{recordId}")
    TestRecord findById(Integer recordId);
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "WHERE r.user_id = #{userId} AND r.is_completed = true " +
            "ORDER BY r.test_date DESC " +
            "LIMIT 1")
    List<TestRecord> findByUserId(Integer userId);
    
    @Insert("INSERT INTO test_records(user_id, test_date, e_score, i_score, " +
            "s_score, n_score, t_score, f_score, j_score, p_score, personality_type, is_completed) " +
            "VALUES(#{userId}, #{testDate}, #{eScore}, #{iScore}, #{sScore}, " +
            "#{nScore}, #{tScore}, #{fScore}, #{jScore}, #{pScore}, #{personalityType}, #{isCompleted})")
    @Options(useGeneratedKeys = true, keyProperty = "recordId")
    int insert(TestRecord record);
    
    @Update("UPDATE test_records SET e_score = #{eScore}, i_score = #{iScore}, " +
            "s_score = #{sScore}, n_score = #{nScore}, t_score = #{tScore}, " +
            "f_score = #{fScore}, j_score = #{jScore}, p_score = #{pScore}, " +
            "personality_type = #{personalityType}, is_completed = #{isCompleted} " +
            "WHERE record_id = #{recordId}")
    int update(TestRecord record);
    
    @Delete("DELETE FROM test_records WHERE record_id = #{recordId}")
    int deleteById(Integer recordId);
    
    /**
     * 根据组织ID和组织类型获取已完成的测试记录
     * 
     * @param orgId 组织ID，如果为null则获取全部数据
     * @param orgType 组织类型，如SCHOOL, DEPARTMENT, MAJOR, CLASS
     * @return 已完成的测试记录列表
     */
    default List<TestRecord> findCompletedRecordsByOrg(Integer orgId, String orgType) {
        if (orgId == null) {
            return findAllCompletedRecords();
        }
        
        if ("SCHOOL".equals(orgType)) {
            return findAllCompletedRecords();
        } else if ("DEPARTMENT".equals(orgType)) {
            return findCompletedRecordsByDepartment(orgId);
        } else if ("MAJOR".equals(orgType)) {
            return findCompletedRecordsByMajor(orgId);
        } else if ("CLASS".equals(orgType)) {
            return findCompletedRecordsByClass(orgId);
        }
        
        return findAllCompletedRecords();
    }
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name, " +
            "d.name as department_name, m.name as major_name, c.name as class_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "JOIN organizations c ON u.org_id = c.org_id " +
            "JOIN organizations m ON c.parent_id = m.org_id " +
            "JOIN organizations d ON m.parent_id = d.org_id " +
            "WHERE r.is_completed = true " +
            "AND r.record_id IN (SELECT MAX(tr.record_id) FROM test_records tr GROUP BY tr.user_id) " +
            "ORDER BY r.test_date DESC")
    List<TestRecord> findAllCompletedRecords();
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name, " +
            "d.name as department_name, m.name as major_name, c.name as class_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "JOIN organizations c ON u.org_id = c.org_id " +
            "JOIN organizations m ON c.parent_id = m.org_id " +
            "JOIN organizations d ON m.parent_id = d.org_id " +
            "WHERE r.is_completed = true AND d.org_id = #{departmentId} " +
            "AND r.record_id IN (SELECT MAX(tr.record_id) FROM test_records tr GROUP BY tr.user_id) " +
            "ORDER BY r.test_date DESC")
    List<TestRecord> findCompletedRecordsByDepartment(Integer departmentId);
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name, " +
            "d.name as department_name, m.name as major_name, c.name as class_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "JOIN organizations c ON u.org_id = c.org_id " +
            "JOIN organizations m ON c.parent_id = m.org_id " +
            "JOIN organizations d ON m.parent_id = d.org_id " +
            "WHERE r.is_completed = true AND m.org_id = #{majorId} " +
            "AND r.record_id IN (SELECT MAX(tr.record_id) FROM test_records tr GROUP BY tr.user_id) " +
            "ORDER BY r.test_date DESC")
    List<TestRecord> findCompletedRecordsByMajor(Integer majorId);
    
    @Select("SELECT r.*, u.usercode, u.full_name as user_full_name, " +
            "d.name as department_name, m.name as major_name, c.name as class_name " +
            "FROM test_records r " +
            "JOIN users u ON r.user_id = u.user_id " +
            "JOIN organizations c ON u.org_id = c.org_id " +
            "JOIN organizations m ON c.parent_id = m.org_id " +
            "JOIN organizations d ON m.parent_id = d.org_id " +
            "WHERE r.is_completed = true AND c.org_id = #{classId} " +
            "AND r.record_id IN (SELECT MAX(tr.record_id) FROM test_records tr GROUP BY tr.user_id) " +
            "ORDER BY r.test_date DESC")
    List<TestRecord> findCompletedRecordsByClass(Integer classId);
} 